# -*- coding: utf-8 -*-
# @Time    : 2020/7/2 15:24
# @FileName: mysql_data_process.py
# @Software: PyCharm
import configparser
import os
import re
import datetime
from configparser import ConfigParser
import pymssql

conn = None
cursor = None


class sqlserver_data_process:
    def __init__(self):
        self.host = "127.0.0.1:1433"
        self.user = 'db_final'
        self.password = 'Czw2436111'
        self.database = "1_homework"
        self.charset = "GBK"

        self.connect_sqlserver()

    def connect_sqlserver(self):
        global conn
        global cursor
        conn = pymssql.connect(self.host, self.user, self.password, self.database, charset=self.charset)
        cursor = conn.cursor()
        if cursor:
            print("数据库连接成功")
        else:
            print("数据库连接失败")

    def run_store_produce(self, sp_exe_state):
        # global cursor
        try:
            cur = conn.cursor()
            cur.execute(sp_exe_state)
            result = cur.fetchall()
            conn.commit()
            cur.close()
            return result
        except Exception as e:
            # print(e, self.host)
            return e

    def select_all_from_table(self, table_name):
        try:
            cur = conn.cursor()
            sqlstr = "select * from " + table_name
            cur.execute(sqlstr)
            data = cur.fetchall()
            cur.close()
            print(data)
            return data
        except Exception as e:
            print(e, self.host)

    def is_table_exists(self, table_name):
        global cursor
        sql = "show tables;"
        cursor.execute(sql)
        tables = [cursor.fetchall()]
        table_list = re.findall('(\'.*?\')', str(tables))
        table_list = [re.sub("'", '', each) for each in table_list]
        print(table_list)
        if table_name in table_list:
            return True
        else:
            return False

    def create_table(self, table_name):
        table_name = local_dict[table_name] + "_temperature"
        if self.is_table_exists(table_name) is not True:
            global cursor
            # 创建数据库SQL语句
            SQL = """CREATE TABLE {} (
             city  varchar(255),
             max_temperature  varchar(255),
             min_temperature  varchar(255),
             ave_temperature  varchar(255),
             precipitation    varchar(255),
             date varchar(255))""".format(table_name)
            cursor.execute(SQL)
            print("创建新表成功")
        else:
            print("创建失败")

    def change_manager_power(self, role_name, role1, role2, role3, change_user_index):
        global cursor
        global conn

        SQL = "select * from manager_info"
        cursor.execute(SQL)
        data = cursor.fetchall()

        SQL = "update manager_info set manager_name = '{}', " \
              "can_delete = '{}', can_add = '{}', can_look='{}' where manager_name = '{}' and can_delete = '{}' and " \
              "can_add = '{}' and can_look = '{}' ".format(role_name, role1, role2, role3,
                                                           data[int(change_user_index) - 1][0],
                                                           data[int(change_user_index) - 1][3],
                                                           data[int(change_user_index) - 1][4],
                                                           data[int(change_user_index) - 1][5])

        print(SQL)
        cursor.execute(SQL)
        conn.commit()
        return self

    def add_commit(self, user_name, mail, commit):
        global cursor
        global conn

        SQL = "select * from commit"
        cursor.execute(SQL)
        data = cursor.fetchall()

        SQL = "INSERT INTO commit(user_name, commit_time, commits, user_mail) VALUES (" \
              "'{}', '{}','{}','{}')".format(user_name, str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')),
                                             commit, mail)
        print(SQL)
        cursor.execute(SQL)
        conn.commit()

    def search_order(self, shop_id, item_id, factory_id, date):  # 查找订单信息
        global cursor
        global conn

        SQL = "select shop_id from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        shopid = cursor.fetchall()
        SQL = "select item_id from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        itemid = cursor.fetchall()
        SQL = "select factory_id from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        factoryid = cursor.fetchall()
        SQL = "select order_sum from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        ordersum = cursor.fetchall()
        SQL = "select sell_sum from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        sellsum = cursor.fetchall()
        SQL = "select product_date from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        productdate = cursor.fetchall()
        SQL = "select bad_item from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        bad = cursor.fetchall()
        SQL = "select order_batch from factory_shop_stock where factory_shop_stock.shop_id ='{}' " \
              "and factory_shop_stock.item_id = '{}' and factory_shop_stock.factory_id = '{}' " \
              "and factory_shop_stock.product_date = '{}' ".format(shop_id, item_id, factory_id, date)
        cursor.execute(SQL)
        batch = cursor.fetchall()

        data = [shopid, itemid, factoryid, ordersum, sellsum, productdate, bad, batch]
        return data

    def display_factory(self):  # 显示工厂
        global cursor
        global conn

        SQL = "select * from factory"
        cursor.execute(SQL)

        data = cursor.fetchall()
        return data

    def get_itemType(self):  # 得到商品类别的销售数量
        global cursor
        global conn

        item_num = [0 for x in range(0, 7)]

        SQL = "EXEC test"
        cursor.execute(SQL)
        item_num[0] = cursor.fetchall()
        print(item_num[0])
        print("asdasdasdads")
        SQL = "select sum(factory_shop_stock.order_sum) from factory_shop_stock,item " \
              "where factory_shop_stock.item_id = item.item_id and item.item_class = '长袖' "
        cursor.execute(SQL)
        item_num[1] = cursor.fetchall()
        SQL = "select sum(factory_shop_stock.order_sum) from factory_shop_stock,item " \
              "where factory_shop_stock.item_id = item.item_id and item.item_class = '长裤' "
        cursor.execute(SQL)
        item_num[2] = cursor.fetchall()
        SQL = "select sum(factory_shop_stock.order_sum) from factory_shop_stock,item " \
              "where factory_shop_stock.item_id = item.item_id and item.item_class = '鞋子' "
        cursor.execute(SQL)
        item_num[3] = cursor.fetchall()
        SQL = "select sum(factory_shop_stock.order_sum) from factory_shop_stock,item " \
              "where factory_shop_stock.item_id = item.item_id and item.item_class = '短裤' "
        cursor.execute(SQL)
        item_num[4] = cursor.fetchall()

        return item_num

    def get_Bad(self):  # 得到坏品数
        global cursor
        global conn
        bad = [0 for x in range(0, 2)]

        SQL = "select sum(factory_shop_stock.bad_item) from factory_shop_stock "
        cursor.execute(SQL)
        bad[0] = cursor.fetchall()
        SQL = "select sum(factory_shop_stock.order_sum)  from factory_shop_stock "
        cursor.execute(SQL)
        bad[1] = cursor.fetchall()

        return bad

    def get_OrderInfo(self):  # 得到订单的信息
        global cursor
        global conn

        SQL = "select * from factory_shop_stock "
        cursor.execute(SQL)
        data = cursor.fetchall()

        return data

    def get_new_product(self):  # 得到最近月份的生产
        global cursor
        global conn
        num = [0 for x in range(0, 7)]

        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-02-01' and  factory_shop_stock.product_date >'2020-01-01' "
        cursor.execute(SQL)
        num[0] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-03-01' and  factory_shop_stock.product_date >'2020-02-01' "
        cursor.execute(SQL)
        num[1] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-04-01' and  factory_shop_stock.product_date >'2020-03-01' "
        cursor.execute(SQL)
        num[2] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-05-01' and  factory_shop_stock.product_date >'2020-04-01' "
        cursor.execute(SQL)
        num[3] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-06-01' and  factory_shop_stock.product_date >'2020-05-01' "
        cursor.execute(SQL)
        num[4] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-07-01' and  factory_shop_stock.product_date >'2020-06-01' "
        cursor.execute(SQL)
        num[5] = cursor.fetchall()
        SQL = "select count(factory_shop_stock.item_id)  from factory_shop_stock  " \
              "where factory_shop_stock.product_date <'2020-08-01' and  factory_shop_stock.product_date >'2020-07-01' "
        cursor.execute(SQL)
        num[6] = cursor.fetchall()

        return num

    def Add_Factory(self, id, name, address):  # 添加工厂
        global cursor
        global conn
        SQL = "INSERTINTO factory (factory_id,factory_name,fctory_address) values ('{}','{}','{}') ".format(id, name,
                                                                                                            address)
        cursor.execute(SQL)
        conn.commit()

    def Delete_Factory(self, id, name, address):  # 删除工厂
        global cursor
        global conn
        SQL = "delete from factory where factory_id = '{}' and factory_name = '{}' ".format(id, name)
        cursor.execute(SQL)
        conn.commit()

    def Update_Factory(self, id, name, address):
        global cursor
        global conn
        SQL = "update factory set factory_name = '{}' ,set factory_address = '{}' where factory_id = '{}' ".format(name,
                                                                                                                   address,
                                                                                                                   id)
        cursor.execute(SQL)
        conn.commit()

    def Search_Factory(self, id, name, address):
        global cursor
        global conn
        SQL = "select * from factory where factory.factory_id = '{}'".format(id)
        cursor.execute()
        data = cursor.fetchall()
        return data

    def delete_comment(self, index):
        global cursor
        global conn

        try:
            index -= 1
            SQL = "select * from commit"
            cursor.execute(SQL)
            data = cursor.fetchall()
            SQL = "delete from commit where user_name='{} 'and commits = '{}' and user_mail='{}'".format(data[index][0],
                                                                                                         data[index][2],
                                                                                                         data[index][3])
            print(data[index])
            cursor.execute(SQL)
            conn.commit()
            return True
        except Exception as e:
            return False
